from pymongo import MongoClient
from tools import *

import re
import sys
import xlrd
import pprint
import datetime

client = MongoClient("localhost", 27017)
db = client.tb_scrapy
boxandneedle_coll = db.boxandneedle

ExportOrderListPath = "data/ExportOrderList.xlsx"
ExportOrderDetailListPath = "data/ExportOrderDetailList.xlsx"

title = "NIKE ZOOM ASSERSION 男子 篮球鞋 欧文3简版 917506-100-004-104"

data = xlrd.open_workbook(ExportOrderDetailListPath)
table = data.sheet_by_index(0)

print('文件读取完毕，准备开始清洗数据')

nrows = table.nrows
ncols = table.ncols

print('ExportOrderDetailList一共有{0}行'.format(nrows))

# 获取订单数据,key为订单id
orderList = getOrderList(ExportOrderListPath, title)

datalist = getDataList(table, nrows, ncols, orderList, title)

productsSummary = summaryProducts(datalist, title)

print('请稍等,数据分析马上开始......')

itemList = getProduct(productsSummary, title)

# 某款宝贝的每个型号的库存、尺码的sku数据
skuData = {}

today = datetime.datetime.now()

# 遍历每一天的库存数据，找到对应的销量数据
for item in boxandneedle_coll.find({"title": re.compile("917506")}):
    date, data, favcount = item["time"], item["data"], item["favcount"]

    for v in data:
        model, size, stock = v["model"], v["size"], v["stock"]

        if not model in skuData:
            skuData[model] = {}

        if not date in skuData[model]:
            skuData[model][date] = {
                "stock": {},
                "sale": {},
                "price": {},
                "favcount": favcount
            }

        if not size in skuData[model][date]["stock"]:
            skuData[model][date]["stock"][size] = 0

        skuData[model][date]["stock"][size] += int(stock)

sizeSales = {}

# 统计各个型号的尺码销量，辅助决策
for item in itemList:
    # 没有颜色分类和尺码数据
    if len(item[5]) == 0:
        continue

    model, size = item[5].split(';')[0].split('：')[1], item[5].split(';')[1].split('：')[1]

    if not model in sizeSales:
        sizeSales[model] = {}

    if not size in sizeSales[model]:
        sizeSales[model][size] = 0

    sizeSales[model][size] += int(item[3])

# 遍历该款式的宝贝的所有历史销售数据
for item in itemList:
    date = item[-1]

    # 没有颜色分类和尺码数据
    if len(item[5]) == 0:
        continue

    model, size, price = item[5].split(';')[0].split('：')[1], item[5].split(';')[1].split('：')[1], float(item[2])

    if model in skuData:
        for key, value in skuData[model].items():
            if key.year == date.year and key.month == date.month and key.day == date.day:
                if not size in value["sale"]:
                    value["sale"][size] = 0

                value["sale"][size] += int(item[3])

                if not size in value["price"]:
                    value["price"][size] = []

                value['price'][size].append(price)
    else:
        pass
        # print(model, "can not match a stock data")

actualSale = 0
predictedSale = 0

for key, _ in skuData.items():
    # 选取记录日期数超过14天(两周)的数据
    if len(skuData[key]) > 14:
        print("*" * 30, key, "*" * 30)

        if key in sizeSales:
            print(sorted(sizeSales[key].items(), key = lambda d: d[1], reverse = True))
            print()

        skdata = []

        # 转化成数组方便后续的计算
        for k, v in skuData[key].items():
            v["time"] = k
            skdata.append(v)

        skuDataInWeeks = []

        if len(skdata) % 7 == 0:
            skdata = skdata[:-1]

        # 把数据按周分组
        for idx, val in enumerate(skdata):
            if idx % 7 == 0:
                skuDataInWeeks.append([])

            skuDataInWeeks[idx // 7].append(val)

        predictedSkuStock = None
        actualSkuSale = None

        historySkuDataInWeeks = []

        for idx, weekData in enumerate(skuDataInWeeks):
            if len(weekData) == 7:
                if len(sys.argv) == 2 and sys.argv[1] == 'detail':
                    for k, v in enumerate(weekData):
                        pprint.pprint(v)
                        print()

                weekStock = countTotalStockByWeek(countTotalSaleByWeek(weekData), skuDataInWeeks[idx + 1][0])
                weekSale = countTotalSaleByWeek(weekData)

                print("库存", weekStock)
                print("销售", weekSale)

                if len(skuDataInWeeks[idx + 1]) == 7:
                    if len(sys.argv) == 2 and sys.argv[1] == 'detail':
                        print('-' * 100)

                    historySkuDataInWeeks.append({
                        "stock": weekStock,
                        "sale": weekSale
                    })
                    print()
                else:
                    predictedSkuStock = weekStock
                    actualSkuSale = weekSale

        print()
        print('预测', predictSkuSaleCount2(historySkuDataInWeeks, predictedSkuStock, actualSkuSale, key, sizeSales[key] if key in sizeSales else None))
